package zy.dao.sell.report.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.report.SellReportDAO;
import zy.dto.sell.allocate.SellAllocateReportDto;
import zy.dto.sell.report.SellStockByShopDepotDto;
import zy.dto.sell.report.SellStockBySizeDto;
import zy.dto.vip.member.ConsumeSnapshotDto;
import zy.entity.sell.cash.T_Sell_Shop;
import zy.entity.sell.cash.T_Sell_ShopList;
import zy.entity.sell.day.T_Sell_Day;
import zy.entity.vip.member.T_Vip_Member;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

/**
 * 零售报表查询 
 */
@Repository
public class SellReportDAOImpl extends BaseDaoImpl implements SellReportDAO{

	@Override
	public Map<String,Object> countShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String jmd = StringUtil.trimString(params.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) count, ");
		sql.append(" SUM(sh_amount) sh_amount,SUM(sh_money) sh_money,SUM(sh_sell_money) sh_sell_money,SUM(sh_cash) sh_cash,");
		sql.append(" SUM(sh_cd_money) sh_cd_money,");
		sql.append(" SUM(sh_vc_money) sh_vc_money,SUM(sh_point_money) sh_point_money,SUM(sh_ec_money) sh_ec_money,");
		sql.append(" SUM(sh_sd_money) sh_sd_money,SUM(sh_bank_money) sh_bank_money,SUM(sh_mall_money) sh_mall_money,");
		sql.append(" SUM(sh_lost_money) sh_lost_money,SUM(sh_wx_money) sh_wx_money,SUM(sh_ali_money) sh_ali_money,");
		sql.append(" SUM(sh_red_money) sh_red_money,SUM(sh_money - sh_vc_money) sh_vc_after_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_base_shop sp ON sh_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND sh_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("sh_shop_code"))){
			params.put("sp_codes", StringUtil.parseList(StringUtil.trimString(params.get("sh_shop_code"))));
			sql.append(" AND sh_shop_code IN (:sp_codes)");
		}
		if(StringUtil.isNotEmpty(params.get("sh_em_code"))){
			sql.append(" AND sh_em_code=:sh_em_code");
		}
		if(StringUtil.isNotEmpty(params.get("sh_number"))){
			sql.append(" AND sh_number=:sh_number");
		}
		if(StringUtil.isNotEmpty(params.get("sh_state"))){
			sql.append(" AND sh_state=:sh_state");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<T_Sell_Shop> listShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String jmd = StringUtil.trimString(params.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append(" sh_id,sh_number,sh_date,sh_em_code,sh_state,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE em_code=sh_em_code AND e.companyid=t.companyid) em_name,");
		sql.append(" sh_amount,sh_money,sh_sell_money,sh_cash,");
		sql.append(" sh_cost_money,sh_upcost_money,sh_cd_money,");
		sql.append(" sh_vc_money,sh_point_money,sh_ec_money,");
		sql.append(" sh_sd_money,sh_bank_money,sh_mall_money,");
		sql.append(" sh_lost_money,sh_wx_money,sh_ali_money,");
		sql.append(" sh_red_money,(sh_money - sh_vc_money) AS sh_vc_after_money,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_base_shop sp ON sh_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND sh_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND sh_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("sh_shop_code"))){
			params.put("sp_codes", StringUtil.parseList(StringUtil.trimString(params.get("sh_shop_code"))));
			sql.append(" AND sh_shop_code IN (:sp_codes)");
		}
		if(StringUtil.isNotEmpty(params.get("sh_em_code"))){
			sql.append(" AND sh_em_code=:sh_em_code");
		}
		if(StringUtil.isNotEmpty(params.get("sh_number"))){
			sql.append(" AND sh_number=:sh_number");
		}
		if(StringUtil.isNotEmpty(params.get("sh_state"))){
			sql.append(" AND sh_state=:sh_state");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY sh_date DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}
	
	@Override
	public List<T_Sell_Shop> listShopByVip(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append(" sh_id,sh_number,sh_date,sh_em_code,sh_state,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE em_code=sh_em_code AND e.companyid=t.companyid) em_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = sh_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" sh_amount,sh_money,sh_sell_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND sh_vip_code=:sh_vip_code");
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY sh_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}

	@Override
	public List<T_Sell_ShopList> listShopList(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT shl_id,shl_number,shl_date,shl_pd_code,");
		sql.append(" pd_no,pd_name,pd_unit,pd_sign_price AS shl_sign_price,");
		sql.append(" shl_cr_code,shl_sz_code,shl_br_code,");
		sql.append(" (SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd_code AND pdm.companyid = p.companyid LIMIT 1) AS pd_img_path,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size s WHERE s.sz_code=t.shl_sz_code AND s.companyid=t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra b WHERE b.br_code=t.shl_br_code AND b.companyid=t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name,");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.shl_main AND e.companyid=t.companyid LIMIT 1) AS main_name,");
		sql.append(" shl_em_code,shl_slave,shl_main,shl_da_code,");
		sql.append(" shl_amount,shl_sell_price,shl_price,");
		sql.append(" shl_money,shl_state,shl_vip_code");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p ON p.pd_code=t.shl_pd_code AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_number = :number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public ConsumeSnapshotDto loadConsumeSnapshot(String vm_code,Integer companyid) {
		ConsumeSnapshotDto dto = new ConsumeSnapshotDto();
		StringBuffer sql = new StringBuffer("");
		try{
			//消费金额、消费次数、上次消费日期、上次消费金额
			sql.append(" SELECT vm_total_money,vm_times,vm_lastbuy_date,vm_lastbuy_money,vm_date");
			sql.append(" FROM t_vip_member");
			sql.append(" WHERE vm_code = :vm_code");
			sql.append(" AND companyid = :companyid");
			sql.append(" LIMIT 1");
			T_Vip_Member member = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("vm_code", vm_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Vip_Member.class));
			dto.setConsume_money(member.getVm_total_money());
			dto.setConsume_times(member.getVm_times());
			dto.setLastbuy_date(member.getVm_lastbuy_date());
			dto.setLastbuy_money(member.getVm_lastbuy_money());
			//消费件数
			sql.setLength(0);
			sql.append(" SELECT SUM(shl_amount)");
			sql.append(" FROM t_sell_shoplist");
			sql.append(" WHERE 1=1");
			sql.append(" AND shl_vip_code = :vm_code");
			sql.append(" AND companyid = :companyid");
			Integer totalAmount = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("vm_code", vm_code).addValue("companyid", companyid) ,Integer.class);
			dto.setConsume_amount(totalAmount);
			//TODO 推荐人数
			dto.setRecommend_amount(0);
			
			//客单价=销售额/成交单数
			if (dto.getConsume_times() != null && dto.getConsume_times().intValue() != 0
					&& dto.getConsume_money() != null) {
				dto.setCustomer_unit_price(dto.getConsume_money() / dto.getConsume_times());
			} else {
				dto.setCustomer_unit_price(0d);
			}
			//物单价=销售额/销售件数
			if (dto.getConsume_amount() != null && dto.getConsume_amount().intValue() != 0
					&& dto.getConsume_money() != null) {
				dto.setUnit_price(dto.getConsume_money() / dto.getConsume_amount());
			} else {
				dto.setUnit_price(0d);
			}
			//连带率=销售数量/成交单数
			if (dto.getConsume_times() != null && dto.getConsume_times().intValue() != 0
					&& dto.getConsume_amount() != null) {
				dto.setJoint_rate(dto.getConsume_amount().doubleValue() / dto.getConsume_times());
			} else {
				dto.setJoint_rate(0d);
			}
			//消费频率=N天/次
			long days = DateUtil.getDaysMinusBetween(member.getVm_date(), DateUtil.getYearMonthDate());
			if (dto.getConsume_times() != null && dto.getConsume_times().intValue() != 0) {
				dto.setConsume_rate((int)days/dto.getConsume_times());
			} else {
				dto.setConsume_rate(0);
			}
		}catch(Exception e){
		}
		return dto;
	}
	
	
	
	
	@Override
	public Map<String,Object> countRetailReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String jmd = StringUtil.trimString(params.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" COUNT(1) count,");
		sql.append(" SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_amount*t.shl_sell_price) shl_sell_money,");
		sql.append(" SUM(shl_money) shl_money");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p ON p.pd_code = t.shl_pd_code AND p.companyid = t.companyid");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = t.shl_shop_code AND shop.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND t.shl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND t.shl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_no"))) {
			sql.append(" AND t.shl_pd_code = :pd_code ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_code"))) {
			sql.append(" AND p.pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("sh_shop_code"))){
			params.put("shop_codes", StringUtil.parseList(StringUtil.trimString(params.get("sh_shop_code"))));
			sql.append(" AND shl_shop_code IN (:shop_codes)");
		}
		if (StringUtil.isNotEmpty(params.get("pd_year"))) {
			sql.append(" AND p.pd_year = :pd_year ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_season"))) {
			sql.append(" AND p.pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_tp_code"))){
			params.put("tp_codes", StringUtil.parseList(StringUtil.trimString(params.get("pd_tp_code"))));
			sql.append(" AND p.pd_tp_code IN (:tp_codes)");
		}
		/*if(StringUtil.isNotEmpty(params.get("pd_tp_upcode"))){
			sql.append(" AND p.pd_tp_upcode=:pd_tp_upcode");
		}*/
		if(StringUtil.isNotEmpty(params.get("pd_bd_code"))){
			params.put("bd_codes", StringUtil.parseList(StringUtil.trimString(params.get("pd_bd_code"))));
			sql.append(" AND p.pd_bd_code IN (:bd_codes)");
		}
		if (StringUtil.isNotEmpty(params.get("sh_em_code"))) {
			sql.append(" AND t.shl_em_code = :sh_em_code ");
		}
		if (StringUtil.isNotEmpty(params.get("vip_code"))) {
			sql.append(" AND t.shl_vip_code = :vip_code ");
		}
		if (StringUtil.isNotEmpty(params.get("supply_code"))) {
			sql.append(" AND p.pd_sp_code = :supply_code ");
		}
		if (StringUtil.isNotEmpty(params.get("minmoney"))) {
			sql.append(" AND p.pd_sell_price BETWEEN :minmoney AND :maxmoney");
		}
		if (StringUtil.isNotEmpty(params.get("shl_state"))) {
			sql.append(" AND t.shl_state = :shl_state ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	/**
	 *零售流水表
	 */
	@Override
	public List<T_Sell_ShopList> retailReportList(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String jmd = StringUtil.trimString(params.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" shl_id,");
		sql.append(" shl_shop_code,");
		sql.append(" (SELECT sp_name FROM t_base_shop s WHERE s.sp_code = t.shl_shop_code AND s.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" shl_number,");
		sql.append(" shl_date,");
		sql.append(" DATE_FORMAT(shl_sysdate,'%Y-%m-%d %T') AS shl_sysdate,");
		sql.append(" shl_state,");
		sql.append(" shl_pd_code,");
		sql.append(" p.pd_name,p.pd_no,");
		sql.append(" (SELECT bd.bd_name FROM t_base_brand bd WHERE bd.bd_code = p.pd_bd_code AND bd.companyid = p.companyid LIMIT 1 ) AS bd_name,");
		sql.append(" (SELECT tp.tp_name FROM t_base_type tp WHERE tp.tp_code = p.pd_tp_code AND tp.companyid = p.companyid LIMIT 1 ) AS tp_name,");
		sql.append(" (SELECT cr.cr_name FROM t_base_color cr WHERE cr.cr_code = t.shl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz.sz_name FROM t_base_size sz WHERE sz.sz_code = t.shl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT ba.br_name FROM t_base_bra ba WHERE ba.br_code = t.shl_br_code AND ba.companyid = t.companyid LIMIT 1 ) AS br_name,");
		sql.append(" p.pd_fabric as pd_fabric,");
		sql.append(" p.pd_season as pd_season,");
		sql.append(" p.pd_year as pd_year,");
		sql.append(" t.shl_amount as shl_amount,");
		sql.append(" t.shl_sell_price as shl_sell_price,");
		sql.append(" t.shl_amount*t.shl_sell_price AS shl_sell_money,");
		sql.append(" t.shl_price as shl_price,");
		sql.append(" (t.shl_price/shl_sell_price) AS shl_price_rate,");
		sql.append(" t.shl_money as shl_money,");
		sql.append(" p.pd_sign_price as pd_sign_price,");
		sql.append(" (SELECT CONCAT(vm_cardcode,'(',vm_name,')') FROM t_vip_member vm WHERE vm_code = shl_vip_code AND vm.companyid = t.companyid LIMIT 1) AS vm_name");
		sql.append(" FROM");
		sql.append(" t_sell_shoplist t");
		sql.append(" JOIN t_base_product p ON p.pd_code = t.shl_pd_code AND p.companyid = t.companyid");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = t.shl_shop_code AND shop.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND t.shl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND t.shl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_no"))) {
			sql.append(" AND t.shl_pd_code = :pd_no ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_code"))) {
			sql.append(" AND p.pd_code = :pd_code ");
		}
		
		if(StringUtil.isNotEmpty(params.get("sh_shop_code"))){
			params.put("shop_codes", StringUtil.parseList(StringUtil.trimString(params.get("sh_shop_code"))));
			sql.append(" AND shl_shop_code IN (:shop_codes)");
		}
		if (StringUtil.isNotEmpty(params.get("pd_year"))) {
			sql.append(" AND p.pd_year >= :pd_year ");
		}
		if (StringUtil.isNotEmpty(params.get("pd_season"))) {
			sql.append(" AND p.pd_season <= :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_tp_code"))){
			params.put("tp_codes", StringUtil.parseList(StringUtil.trimString(params.get("pd_tp_code"))));
			sql.append(" AND p.pd_tp_code IN (:tp_codes)");
		}
		/*if(StringUtil.isNotEmpty(params.get("pd_tp_upcode"))){
			sql.append(" AND p.pd_tp_upcode=:pd_tp_upcode");
		}*/
		if(StringUtil.isNotEmpty(params.get("pd_bd_code"))){
			params.put("bd_codes", StringUtil.parseList(StringUtil.trimString(params.get("pd_bd_code"))));
			sql.append(" AND p.pd_bd_code IN (:bd_codes)");
		}
		if (StringUtil.isNotEmpty(params.get("sh_em_code"))) {
			sql.append(" AND t.shl_em_code = :sh_em_code ");
		}
		if (StringUtil.isNotEmpty(params.get("vip_code"))) {
			sql.append(" AND t.shl_vip_code = :vip_code ");
		}
		if (StringUtil.isNotEmpty(params.get("supply_code"))) {
			sql.append(" AND p.pd_sp_code = :supply_code ");
		}
		if (StringUtil.isNotEmpty(params.get("minmoney"))) {
			sql.append(" AND t.shl_sell_price BETWEEN :minmoney AND :maxmoney");
		}
		if (StringUtil.isNotEmpty(params.get("shl_state"))) {
			sql.append(" AND t.shl_state = :shl_state ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY shl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Sell_ShopList> noRate_list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		Object tp_code = paramMap.get("tp_code");
		Object bd_code = paramMap.get("bd_code");
		Object pd_no = paramMap.get("pd_no");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object minrate = paramMap.get("minrate");
		Object maxrate = paramMap.get("maxrate");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,shl_number,shl_state,shl_sysdate,pd_name,pd_no,pd_unit,shl_cr_code,shl_sz_code,shl_br_code,shl_main,shl_em_code,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size c WHERE c.sz_code=t.shl_sz_code AND c.companyid=t.companyid) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra c WHERE c.br_code=t.shl_br_code AND c.companyid=t.companyid) AS br_name,");
		sql.append(" (SELECT vm_name FROM t_vip_member c WHERE c.vm_code=t.shl_vip_code AND c.companyid=t.companyid) AS vm_name,");
		sql.append(" (SELECT em_name FROM t_base_emp c WHERE c.em_code=t.shl_main AND c.companyid=t.companyid) AS main_name,");
		sql.append(" (SELECT em_name FROM t_base_emp c WHERE c.em_code=t.shl_em_code AND c.companyid=t.companyid) AS em_name,");
		sql.append(" (SELECT tp_name FROM t_base_type c WHERE c.tp_code=p.pd_tp_code AND c.companyid=p.companyid) AS tp_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand c WHERE c.bd_code=p.pd_bd_code AND c.companyid=p.companyid) AS bd_name,");
		sql.append(" shl_amount,shl_sell_price,shl_price,shl_cost_price,shl_upcost_price,shl_money,shl_vip_code,shl_sale_code");
		sql.append(",(shl_sell_price*shl_amount) shl_sell_money,(shl_cost_price*shl_amount) shl_cost_money,(shl_money-(shl_cost_price*shl_amount)) shl_profit ");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND p.pd_no=:pd_no");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(minrate)){
			sql.append(" AND (t.shl_price/t.shl_sell_price) >= :minrate");
		}
		if(StringUtil.isNotEmpty(maxrate)){
			sql.append(" AND (t.shl_price/t.shl_sell_price) <= :maxrate");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY shl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public Map<String, Object> noRate_count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		Object tp_code = paramMap.get("tp_code");
		Object bd_code = paramMap.get("bd_code");
		Object pd_no = paramMap.get("pd_no");
		Object pd_year = paramMap.get("pd_year");
		Object pd_season = paramMap.get("pd_season");
		Object minrate = paramMap.get("minrate");
		Object maxrate = paramMap.get("maxrate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) AS count,SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_amount*shl_sell_price) shl_sell_money,SUM(shl_money) AS shl_money,");
		sql.append(" SUM(shl_amount*shl_cost_price) shl_cost_money,");
		sql.append(" SUM(shl_money)-SUM(shl_amount*shl_cost_price) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(pd_no)){
			sql.append(" AND p.pd_no=:pd_no");
		}
		if(StringUtil.isNotEmpty(pd_year)){
			sql.append(" AND p.pd_year=:pd_year");
		}
		if(StringUtil.isNotEmpty(pd_season)){
			sql.append(" AND p.pd_season=:pd_season");
		}
		if(StringUtil.isNotEmpty(minrate)){
			sql.append(" AND (t.shl_price/t.shl_sell_price) >= :minrate");
		}
		if(StringUtil.isNotEmpty(maxrate)){
			sql.append(" AND (t.shl_price/t.shl_sell_price) <= :maxrate");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> rank_stock(Map<String, Object> paramMap) {
		Object type = paramMap.get("type");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		String code = "";
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		if("brand".equals(StringUtil.trimString(type))){
			code = "bd_code";
		}else{
			code = "tp_code";
		}
		sql.append(" SELECT SUM(sd_amount) sd_amount,pd_"+code+" bd_code,SUM(sd_amount*pd_sell_price) sd_money");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.sd_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_depot d");
		sql.append(" ON d.dp_code=t.sd_dp_code");
		sql.append(" AND d.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=d.dp_shop_code");
		sql.append(" AND s.companyid=d.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pd_"+code);
		return namedParameterJdbcTemplate.queryForList(sql.toString(), paramMap);
	}

	@Override
	public List<T_Sell_ShopList> rank_list(Map<String, Object> paramMap) {
		return namedParameterJdbcTemplate.query(buildRankListSql(paramMap), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}
	private String buildRankListSql(Map<String,Object> paramMap){
		Object type = paramMap.get("type");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object em_code = paramMap.get("em_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		if("brand".equals(StringUtil.trimString(type))){
			sql.append(" SELECT shl_id,bd_code,bd_name,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
			sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,");
			sql.append(" SUM(shl_money)/SUM(shl_amount) shl_avg_price,(SUM(shl_money)-SUM(shl_cost_price*shl_amount)) shl_profit");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.shl_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if(StringUtil.isNotEmpty(shl_shop_code)){
				sql.append(" AND sp_code=:shl_shop_code");
			}
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" JOIN t_base_brand b");
			sql.append(" ON b.bd_code=p.pd_bd_code");
			sql.append(" AND b.companyid=p.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(em_code)){
				sql.append(" AND shl_main=:em_code");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY bd_code");
			sql.append(" ");
			if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
				sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
			}else {
				sql.append(" ORDER BY SUM(shl_money) DESC");
			}
		}else{
			sql.append(" SELECT shl_id,tp_code bd_code,tp_name bd_name,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
			sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,");
			sql.append(" SUM(shl_money)/SUM(shl_amount) shl_avg_price,(SUM(shl_money)-SUM(shl_cost_price*shl_amount)) shl_profit");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.shl_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if(StringUtil.isNotEmpty(shl_shop_code)){
				sql.append(" AND sp_code=:shl_shop_code");
			}
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" JOIN t_base_type b");
			sql.append(" ON b.tp_code=p.pd_tp_code");
			sql.append(" AND b.companyid=p.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(em_code)){
				sql.append(" AND shl_main=:em_code");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY tp_code");
			sql.append(" ");
			if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
				sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
			}else {
				sql.append(" ORDER BY SUM(shl_money) DESC");
			}
		}
		return sql.toString();
	}

	@Override
	public List<T_Sell_ShopList> buyRank(Map<String, Object> paramMap) {
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object sp_code = paramMap.get("sp_code");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sp.sp_code,sp.sp_name,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,");
		sql.append(" (SUM(shl_money)/SUM(shl_amount)) shl_avg_price,SUM(shl_money)-SUM(shl_cost_price*shl_amount) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_buy_supply sp");
		sql.append(" ON sp.sp_code=p.pd_sp_code");
		sql.append(" AND sp.companyid=p.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND s.sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(sp_code)){
			sql.append(" AND pd_sp_code=:sp_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pd_sp_code");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Sell_ShopList> empRank(Map<String, Object> paramMap) {
		Object type = paramMap.get("type");
		Object begindate = paramMap.get("begindate");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		Object em_code = paramMap.get("em_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,em_name,COUNT(DISTINCT shl_number) shl_count,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		if(type.equals("main")){
			sql.append(" t.shl_main shl_em_code,");
		}else{
			sql.append(" t.shl_slave shl_em_code,");
		}
		sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,");
		sql.append(" SUM(shl_money)/SUM(shl_amount) shl_avg_price,(SUM(shl_money)-SUM(shl_cost_price*shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_emp p");
		if(type.equals("main")){
			sql.append(" ON p.em_code=t.shl_main");
		}else{
			sql.append(" ON p.em_code=t.shl_slave");
		}
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(em_code)){
			paramMap.put("em_codes", StringUtil.parseList(StringUtil.trimString(em_code)));
			sql.append(" AND em_code IN (:em_codes)");
		}
		sql.append(" AND t.companyid=:companyid");
		if(type.equals("main")){
			sql.append(" GROUP BY t.shl_main");
		}else{
			sql.append(" GROUP BY t.shl_slave");
		}
		if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY SUM(shl_money) DESC");
		}
		
		List<T_Sell_ShopList> list = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
		if(null != list && list.size() > 0){
			sql.setLength(0);
			sql.append(" SELECT COUNT(1) da_receive,re_em_code em_code");
			sql.append(" FROM t_sell_receive t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.re_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if(StringUtil.isNotEmpty(shl_shop_code)){
				sql.append(" AND sp_code=:shl_shop_code");
			}
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.re_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.re_type=1");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY re_em_code");
			List<T_Sell_Day> receList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_Day.class));
			
			sql.setLength(0);
			sql.append(" SELECT COUNT(1) da_try,tr_em_code em_code");
			sql.append(" FROM t_sell_try t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.tr_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if(StringUtil.isNotEmpty(shl_shop_code)){
				sql.append(" AND sp_code=:shl_shop_code");
			}
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.tr_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY tr_em_code");
			List<T_Sell_Day> tryList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_Day.class));
			sql.setLength(0);
			sql.append(" SELECT vm_manager_code em_code,COUNT(1) sh_count");
			sql.append(" FROM t_vip_member t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.vm_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if(StringUtil.isNotEmpty(shl_shop_code)){
				sql.append(" AND sp_code=:shl_shop_code");
			}
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.vm_sysdate BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY t.vm_manager_code");
			List<T_Sell_Day> vipList = namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Sell_Day.class));
			buildList(list,receList,tryList,vipList);
		}
		return list;
	}
	private void buildList(List<T_Sell_ShopList> list,List<T_Sell_Day> receList,
			List<T_Sell_Day> tryList,List<T_Sell_Day> vipList){
		for(T_Sell_ShopList item:list){
			if(null != receList && receList.size() > 0){
				for(T_Sell_Day day:receList){
					if(item.getShl_em_code().equals(day.getEm_code())){
						item.setDa_receive(day.getDa_receive());
					}
				}
			}
			if(null != tryList && tryList.size() > 0){
				for(T_Sell_Day day:tryList){
					if(item.getShl_em_code().equals(day.getEm_code())){
						item.setDa_try(day.getDa_try());
					}
				}
			}
			if(null != vipList && vipList.size() > 0){
				for(T_Sell_Day day:vipList){
					if(item.getShl_em_code().equals(day.getEm_code())){
						item.setShl_vips(day.getSh_count());
					}
				}
			}
		}
	}
	
	@Override
	public Map<String, Object> styleRank_count(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object sz_code = paramMap.get("sz_code");
		Object br_code = paramMap.get("br_code");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1),SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_money) shl_money,SUM(shl_sell_money) shl_sell_money,");
		sql.append(" SUM(shl_cost_money) shl_cost_money,SUM(shl_profit) shl_profit");
		sql.append(" FROM (SELECT");
		sql.append(" SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price * shl_amount) shl_sell_money,");
		sql.append(" SUM(shl_cost_price * shl_amount) shl_cost_money,");
		sql.append(" SUM(shl_money) - SUM(shl_cost_price * shl_amount) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s ");
		sql.append(" ON s.sp_code = t.shl_shop_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE	1 = 1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid =:companyid");
		sql.append(" GROUP BY shl_cr_code");
		if(StringUtil.isNotEmpty(sz_code) && sz_code.equals("1")){
			sql.append(",shl_sz_code");
		}
		if(StringUtil.isNotEmpty(br_code) && br_code.equals("1")){
			sql.append(",shl_br_code");
		}
		sql.append(") tmp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	@Override
	public List<T_Sell_ShopList> styleRank_list(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object sz_code = paramMap.get("sz_code");
		Object br_code = paramMap.get("br_code");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT shl_id,");
		sql.append(" shl_cr_code,");
		sql.append(" (SELECT cr_name from t_base_color c where c.cr_code=t.shl_cr_code AND c.companyid=t.companyid limit 1) cr_name,");
		if(StringUtil.isNotEmpty(sz_code)  && sz_code.equals("1")){
			sql.append(" shl_sz_code,");
			sql.append(" (SELECT sz_name from t_base_size c where c.sz_code=t.shl_sz_code AND c.companyid=t.companyid limit 1) sz_name,");
		}
		if(StringUtil.isNotEmpty(br_code)  && br_code.equals("1")){
			sql.append(" shl_br_code,");
			sql.append(" (SELECT br_name from t_base_bra c where c.br_code=t.shl_br_code AND c.companyid=t.companyid limit 1) br_name,");
		}
		sql.append(" SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price * shl_amount) shl_sell_money,");
		sql.append(" SUM(shl_cost_price * shl_amount) shl_cost_money,");
		sql.append(" (SUM(shl_money) / SUM(shl_amount)) shl_avg_price,");
		sql.append(" SUM(shl_money) - SUM(shl_cost_price * shl_amount) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s ");
		sql.append(" ON s.sp_code = t.shl_shop_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE	1 = 1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid =:companyid");
		sql.append(" GROUP BY shl_cr_code");
		if(StringUtil.isNotEmpty(sz_code)  && sz_code.equals("1")){
			sql.append(",shl_sz_code");
		}
		if(StringUtil.isNotEmpty(br_code)  && br_code.equals("1")){
			sql.append(",shl_br_code");
		}
		sql.append(" ORDER BY shl_amount DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Sell_ShopList> noRank_list(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object em_code = paramMap.get("em_code");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,pd_name,pd_no,pd_date,shl_pd_code,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,shl_cost_price,");
		sql.append(" SUM(shl_money)/SUM(shl_amount) shl_avg_price,(SUM(shl_money)-SUM(shl_cost_price*shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(em_code)){
			sql.append(" AND t.shl_main=:em_code");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY SUM(shl_money) DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}
	
	@Override
	public Map<String, Object> noRank_count(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object em_code = paramMap.get("em_code");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_money) shl_sell_money,SUM(shl_cost_money) shl_cost_money,");
		sql.append(" SUM(shl_profit) shl_profit,SUM(sd.sd_amount) sd_amount,count(1) count");
		sql.append(" FROM ");
		sql.append(" (SELECT");
		sql.append(" t.companyid,");
		sql.append(" shl_pd_code,");
		sql.append(" SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price * shl_amount) shl_sell_money,");
		sql.append(" SUM(shl_cost_price * shl_amount) shl_cost_money,");
		sql.append(" (SUM(shl_money) - SUM(shl_cost_price * shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s ON s.sp_code = t.shl_shop_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(em_code)){
			sql.append(" AND t.shl_main=:em_code");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY shl_pd_code");
		sql.append(" ORDER BY SUM(shl_money) DESC) temp");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT sd_pd_code,SUM(sd_amount) sd_amount,d.companyid");
		sql.append(" FROM t_stock_data d ");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=d.sd_pd_code");
		sql.append(" AND p.companyid=d.companyid");
		sql.append(" JOIN t_base_depot dp ");
		sql.append(" ON sd_dp_code=dp_code");
		sql.append(" AND dp.companyid=d.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON dp_shop_code=s.sp_code");
		sql.append(" AND s.companyid = d.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			sql.append(" AND sp_code=:shl_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		sql.append(" AND d.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		sql.append(" ) sd");
		sql.append(" ON temp.shl_pd_code = sd.sd_pd_code");
		sql.append(" AND temp.companyid = sd.companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}


	@Override
	public Map<String, Object> listRank_count(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object em_code = paramMap.get("em_code");
		Object type = paramMap.get("type");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_money) shl_sell_money,SUM(shl_cost_money) shl_cost_money,");
		sql.append(" SUM(shl_profit) shl_profit,count(1) count");
		sql.append(" FROM ");
		sql.append(" (SELECT");
		sql.append(" shl_pd_code,");
		sql.append(" SUM(shl_amount) shl_amount,");
		sql.append(" SUM(shl_money) shl_money,");
		sql.append(" SUM(shl_sell_price * shl_amount) shl_sell_money,");
		sql.append(" SUM(shl_cost_price * shl_amount) shl_cost_money,");
		sql.append(" (SUM(shl_money) - SUM(shl_cost_price * shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s ON s.sp_code = t.shl_shop_code");
		sql.append(" AND s.companyid = t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(em_code)){
			sql.append(" AND t.shl_main=:em_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if("list".equals(StringUtil.trimString(type))){
			sql.append(" GROUP BY shl_sub_code");
		}else{
			sql.append(" GROUP BY pd_code");
		}
		sql.append(" ORDER BY SUM(shl_money) DESC) temp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	@Override
	public List<T_Sell_ShopList> listRank_list(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object em_code = paramMap.get("em_code");
		Object type = paramMap.get("type");
		Object shl_shop_code = paramMap.get("shl_shop_code");
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT shl_id,pd_name,pd_no,pd_date,shl_pd_code,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money,");
		if("list".equals(StringUtil.trimString(type))){
			sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=t.shl_cr_code AND c.companyid=t.companyid LIMIT 1) cr_name,");
			sql.append(" (SELECT sz_name FROM t_base_size c WHERE c.sz_code=t.shl_sz_code AND c.companyid=t.companyid LIMIT 1) sz_name,");
			sql.append(" (SELECT br_name FROM t_base_bra c WHERE c.br_code=t.shl_br_code AND c.companyid=t.companyid LIMIT 1) br_name,");
		}
		sql.append(" SUM(shl_sell_price*shl_amount) shl_sell_money,SUM(shl_cost_price*shl_amount) shl_cost_money,shl_cost_price,");
		sql.append(" SUM(shl_money)/SUM(shl_amount) shl_avg_price,(SUM(shl_money)-SUM(shl_cost_price*shl_amount)) shl_profit");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.shl_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(shl_shop_code)){
			paramMap.put("sp_codes", StringUtil.parseList(StringUtil.trimString(shl_shop_code)));
			sql.append(" AND sp_code IN (:sp_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=t.shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(bd_code)){
			paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
			sql.append(" AND pd_bd_code IN (:bd_codes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
			sql.append(" AND pd_tp_code IN (:tp_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("pd_code"))){
			sql.append(" AND pd_code=:pd_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
		}
		if(StringUtil.isNotEmpty(em_code)){
			sql.append(" AND t.shl_main=:em_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if("list".equals(StringUtil.trimString(type))){
			sql.append(" GROUP BY shl_sub_code");
		}else{
			sql.append(" GROUP BY pd_code");
		}
		if(StringUtil.isNotEmpty(paramMap.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(paramMap.get(CommonUtil.SIDX)).append(" ").append(paramMap.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY SUM(shl_money) DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Sell_Shop> shopRank(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object begindate = paramMap.get("begindate");
		Object sh_shop_code = paramMap.get("sh_shop_code");
		String jmd = StringUtil.trimString(paramMap.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" sh_shop_code,sp_name shop_name,SUM(sh_amount) sh_amount,COUNT(1) sh_count,");  
		sql.append(" SUM(sh_cost_money) sh_cost_money,SUM(sh_sell_money) sh_sell_money,");	
		sql.append(" SUM(sh_money) sh_money,SUM(sh_money)-SUM(sh_cost_money) sh_profit,SUM(sh_money) - SUM(sh_vc_money) sh_vc_after_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.sh_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(sh_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1=1");
			sql.append(" AND sp_code=:shop_code");
		}
		
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.sh_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY sh_shop_code");
		sql.append(" ORDER BY SUM(sh_money) DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}

	@Override
	public List<T_Sell_Shop> month(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object date = paramMap.get("date");
		Object sh_shop_code = paramMap.get("sh_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT DAY(sh_date) sh_date,COUNT(1) sh_count,SUM(sh_cost_money) sh_cost_money,SUM(sh_sell_money) sh_sell_money,");
		sql.append(" SUM(IFNULL(sh_lost_money,0)) ,SUM(IFNULL(sh_cd_money*sh_cd_rate,0)) sh_cd_money,");
		sql.append(" SUM(IFNULL(sh_vc_money*sh_vc_rate,0)) sh_vc_money,SUM(IFNULL(sh_ec_money,0)) sh_ec_money,");
		sql.append(" (SUM(sh_money)/SUM(sh_amount)) sh_avg_price,SUM(IFNULL(sh_point_money,0)) sh_point_money,");
		sql.append(" SUM(sh_sell_money) sh_sell_money,SUM(sh_money) sh_money,SUM(sh_amount) sh_amount");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.sh_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(sh_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(date)){
			sql.append(" AND INSTR(t.sh_date,:date)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY DAY(sh_date)");

		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}
	@Override
	public List<T_Sell_Shop> year(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object date = paramMap.get("date");
		Object sh_shop_code = paramMap.get("sh_shop_code");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT MONTH(sh_date) sh_date,COUNT(1) sh_count,SUM(sh_cost_money) sh_cost_money,SUM(sh_sell_money) sh_sell_money,");
		sql.append(" SUM(IFNULL(sh_lost_money,0)) ,SUM(IFNULL(sh_cd_money*sh_cd_rate,0)) sh_cd_money,");
		sql.append(" SUM(IFNULL(sh_vc_money*sh_vc_rate,0)) sh_vc_money,SUM(IFNULL(sh_ec_money,0)) sh_ec_money,");
		sql.append(" (SUM(sh_money)/SUM(sh_amount)) sh_avg_price,SUM(IFNULL(sh_point_money,0)) sh_point_money,");
		sql.append(" SUM(sh_sell_money) sh_sell_money,SUM(sh_money) sh_money,SUM(sh_amount) sh_amount");
		sql.append(" FROM t_sell_shop t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.sh_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			sql.append(" AND sp_code=:sh_shop_code");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(date)){
			sql.append(" AND INSTR(t.sh_date,:date)>0");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY MONTH(sh_date)");

		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}

	@Override
	public List<T_Sell_ShopList> shopSell(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object bd_code = paramMap.get("bd_code");
		Object tp_code = paramMap.get("tp_code");
		Object begindate = paramMap.get("begindate");
		Object type = paramMap.get("type");
		StringBuffer sql = new StringBuffer();
		if(StringUtil.trimString(type).equals("brand")){
			sql.append(" SELECT shl_id,shl_shop_code,pd_bd_code bd_code,bd_name,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.shl_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" JOIN t_base_brand b");
			sql.append(" ON b.bd_code=p.pd_bd_code");
			sql.append(" AND b.companyid=p.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY bd_code,shl_shop_code");
			sql.append(" ORDER BY bd_code,shl_shop_code");
		}else{
			sql.append(" SELECT shl_id,shl_shop_code,pd_tp_code bd_code,tp_name bd_name,SUM(shl_amount) shl_amount,SUM(shl_money) shl_money");
			sql.append(" FROM t_sell_shoplist t");
			sql.append(" JOIN t_base_shop s");
			sql.append(" ON s.sp_code=t.shl_shop_code");
			sql.append(" AND s.companyid=t.companyid");
			if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 1));
			}else{//自营、加盟、合伙
				sql.append(" AND sp_code = :shop_code");
			}
			sql.append(" JOIN t_base_product p");
			sql.append(" ON p.pd_code=t.shl_pd_code");
			sql.append(" AND p.companyid=t.companyid");
			sql.append(" JOIN t_base_type b");
			sql.append(" ON b.tp_code=p.pd_tp_code");
			sql.append(" AND b.companyid=p.companyid");
			sql.append(" WHERE 1=1");
			if(StringUtil.isNotEmpty(bd_code)){
				paramMap.put("bd_codes", StringUtil.parseList(StringUtil.trimString(bd_code)));
				sql.append(" AND pd_bd_code IN (:bd_codes)");
			}
			if(StringUtil.isNotEmpty(tp_code)){
				paramMap.put("tp_codes", StringUtil.parseList(StringUtil.trimString(tp_code)));
				sql.append(" AND pd_tp_code IN (:tp_codes)");
			}
			if(StringUtil.isNotEmpty(begindate)){
				sql.append(" AND t.shl_date BETWEEN :begindate AND :enddate");
			}
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY tp_code,shl_shop_code");
			sql.append(" ORDER BY tp_code,shl_shop_code");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public Integer sell_allocate_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		List<String> in_shop_codes = (List<String>)params.get("in_shop_codes");
		List<String> out_shop_codes = (List<String>)params.get("out_shop_codes");
		Object pd_no = params.get("pd_no");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pd_no)) {
			sql.append(" AND pd.pd_no = :pd_no ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		
		//调入店铺条件
		if (in_shop_codes != null && in_shop_codes.size() > 0) {
			if(in_shop_codes.size() == 1){
				sql.append(" AND ac_in_shop = ").append(in_shop_codes.get(0));
			}else{
				sql.append(" AND ac_in_shop IN(");
				for (int i = 0; i < in_shop_codes.size(); i++) {
					sql.append(in_shop_codes.get(i));
					if(i < in_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		//调出店铺条件
		if (out_shop_codes != null && out_shop_codes.size() > 0) {
			if(out_shop_codes.size() == 1){
				sql.append(" AND ac_out_shop = ").append(out_shop_codes.get(0));
			}else{
				sql.append(" AND ac_out_shop IN(");
				for (int i = 0; i < out_shop_codes.size(); i++) {
					sql.append(out_shop_codes.get(i));
					if(i < out_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
		}else if (CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)) {// 自营店或合伙店
			sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<SellAllocateReportDto> sell_allocate(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		List<String> in_shop_codes = (List<String>)params.get("in_shop_codes");
		List<String> out_shop_codes = (List<String>)params.get("out_shop_codes");
		Object pd_no = params.get("pd_no");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ac_id,ac_number,ac_date,ac_out_shop,ac_in_shop,ac_state,ac_sysdate,ac_man,ac_receiver,ac_recedate,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_out_shop AND sp.companyid = t.companyid LIMIT 1) AS outshop_name,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = ac_in_shop AND sp.companyid = t.companyid LIMIT 1) AS inshop_name,");
		sql.append(" pd_no,pd_name,pd_unit,acl_id,acl_pd_code,acl_amount,acl_sell_price,acl_cost_price,acl_in_sell_price,acl_in_cost_price,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pd_no)) {
			sql.append(" AND pd.pd_no = :pd_no ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		
		//调入店铺条件
		if (in_shop_codes != null && in_shop_codes.size() > 0) {
			if(in_shop_codes.size() == 1){
				sql.append(" AND ac_in_shop = ").append(in_shop_codes.get(0));
			}else{
				sql.append(" AND ac_in_shop IN(");
				for (int i = 0; i < in_shop_codes.size(); i++) {
					sql.append(in_shop_codes.get(i));
					if(i < in_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		//调出店铺条件
		if (out_shop_codes != null && out_shop_codes.size() > 0) {
			if(out_shop_codes.size() == 1){
				sql.append(" AND ac_out_shop = ").append(out_shop_codes.get(0));
			}else{
				sql.append(" AND ac_out_shop IN(");
				for (int i = 0; i < out_shop_codes.size(); i++) {
					sql.append(out_shop_codes.get(i));
					if(i < out_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
		}else if (CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)) {// 自营店或合伙店
			sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ac_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellAllocateReportDto.class));
	}

	@Override
	public Map<String, Object> sell_allocate_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object ac_state = params.get("ac_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		List<String> in_shop_codes = (List<String>)params.get("in_shop_codes");
		List<String> out_shop_codes = (List<String>)params.get("out_shop_codes");
		Object pd_no = params.get("pd_no");
		Object ac_number = params.get("ac_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" IFNULL(SUM(acl_amount),0) AS acl_amount, ");
		sql.append(" IFNULL(SUM(acl_amount*acl_sell_price),0) AS acl_sell_money, ");
		sql.append(" IFNULL(SUM(acl_amount*acl_cost_price),0) AS acl_cost_money, ");
		sql.append(" IFNULL(SUM(acl_amount*acl_in_sell_price),0) AS acl_in_sell_money, ");
		sql.append(" IFNULL(SUM(acl_amount*acl_in_cost_price),0) AS acl_in_cost_money ");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" JOIN t_sell_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(ac_state)) {
			sql.append(" AND ac_state = :ac_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ac_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ac_sysdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pd_no)) {
			sql.append(" AND pd.pd_no = :pd_no ");
		}
		if (StringUtil.isNotEmpty(ac_number)) {
			sql.append(" AND INSTR(ac_number,:ac_number) > 0 ");
		}
		
		//调入店铺条件
		if (in_shop_codes != null && in_shop_codes.size() > 0) {
			if(in_shop_codes.size() == 1){
				sql.append(" AND ac_in_shop = ").append(in_shop_codes.get(0));
			}else{
				sql.append(" AND ac_in_shop IN(");
				for (int i = 0; i < in_shop_codes.size(); i++) {
					sql.append(in_shop_codes.get(i));
					if(i < in_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		//调出店铺条件
		if (out_shop_codes != null && out_shop_codes.size() > 0) {
			if(out_shop_codes.size() == 1){
				sql.append(" AND ac_out_shop = ").append(out_shop_codes.get(0));
			}else{
				sql.append(" AND ac_out_shop IN(");
				for (int i = 0; i < out_shop_codes.size(); i++) {
					sql.append(out_shop_codes.get(i));
					if(i < out_shop_codes.size()-1){
						sql.append(",");
					}
				}
				sql.append(") ");
			}
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
		}else if (CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)) {// 自营店或合伙店
			sql.append(" AND (ac_out_shop = :shop_code OR ac_in_shop = :shop_code)");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public String getSupply(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT");
		sql.append("(select sp_name from t_buy_supply sp where sp.sp_code=t.pd_sp_code and sp.companyid=t.companyid LIMIT 1) as sp_name ");//供货商
		sql.append(" FROM t_base_product t");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, String.class);
	}
	
	@Override
	public List<SellStockByShopDepotDto> query_stock_detail(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cr_name,cr_code,sz_code,sz_name,IFNULL(br_code,'') AS br_code,IFNULL(br_name,'') AS br_name,");
		sql.append(" CONCAT(pd_code,cr_code,sz_code,IFNULL(br_code,'')) AS sub_code");
		sql.append(" FROM t_base_product t");
		sql.append(" JOIN t_base_product_color pdc ON pdc_pd_code = pd_code AND pdc.companyid = t.companyid");
		sql.append(" JOIN t_base_color cr ON cr_code = pdc_cr_code AND cr.companyid = pdc.companyid");
		sql.append(" JOIN t_base_sizelist szl ON szl_szg_code = pd_szg_code AND szl.companyid = t.companyid");
		sql.append(" JOIN t_base_size sz ON szl_sz_code = sz_code AND szl.companyid = sz.companyid");
		sql.append(" LEFT JOIN t_base_product_br pdb ON pdb_pd_code = pd_code AND pdb.companyid = pdc.companyid");
		sql.append(" LEFT JOIN t_base_bra br ON br_code = pdb_br_code AND br.companyid = pdc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY cr_code,sz_code,br_code");
		final List<SellStockByShopDepotDto> dtos = namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(SellStockByShopDepotDto.class));
		
		//销售数量
		sql.setLength(0);
		sql.append(" SELECT shl_sub_code,GROUP_CONCAT(CONCAT(shl_shop_code,'#',amount)) AS shop_amount");
		sql.append(" FROM(");
		sql.append(" SELECT shl_sub_code,shl_shop_code,SUM(shl_amount) AS amount");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND shl_date >= :begindate");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND shl_date <= :enddate");
		}
		if(StringUtil.isNotEmpty(params.get("em_code"))){
			sql.append(" AND shl_main = :em_code");
		}
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_pd_code = :pd_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY shl_sub_code,shl_shop_code");
		sql.append(" )temp");
		sql.append(" GROUP BY shl_sub_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("shl_sub_code");
				String shop_amount = rs.getString("shop_amount");
				if(StringUtil.isEmpty(shop_amount)){
					return null;
				}
				String[] shopAmounts = shop_amount.split(",");
				for (SellStockByShopDepotDto dto : dtos) {
					if(!sub_code.equals(dto.getSub_code())){
						continue;
					}
					for (String shopAmount : shopAmounts) {
						String[] temps = shopAmount.split("#");
						dto.getShop_SellAmountMap().put(temps[0], Integer.parseInt(temps[1]));
					}
				}
				return null;
			};
		});
		//店铺库存数量
		
		sql.setLength(0);
		sql.append(" SELECT sd_code,GROUP_CONCAT(CONCAT(dp_shop_code,'#',amount)) AS shop_amount");
		sql.append(" FROM(");
		sql.append(" SELECT sd_code,dp_shop_code,SUM(sd_amount) AS amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND dp_shop_code IN(:shopCodes)");
		sql.append(" AND sd_pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sd_code,dp_shop_code");
		sql.append(" )temp");
		sql.append(" GROUP BY sd_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("sd_code");
				String shop_amount = rs.getString("shop_amount");
				if(StringUtil.isEmpty(shop_amount)){
					return null;
				}
				String[] shopAmounts = shop_amount.split(",");
				for (SellStockByShopDepotDto dto : dtos) {
					if(!sub_code.equals(dto.getSub_code())){
						continue;
					}
					for (String shopAmount : shopAmounts) {
						String[] temps = shopAmount.split("#");
						dto.getShop_StockAmountMap().put(temps[0], Integer.parseInt(temps[1]));
					}
				}
				return null;
			};
		});
		//在途数量
		sql.setLength(0);
		sql.append(" SELECT wtl_sub_code,GROUP_CONCAT(CONCAT(wt_shop_code,'#',amount)) AS shop_amount");
		sql.append(" FROM(");
		sql.append(" SELECT wtl_sub_code,wt_shop_code,SUM(wtl_sendamount) AS amount");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wt_number = wtl_number AND wtl.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND wt_ar_state = 3");
		sql.append(" AND wt_type = 0");
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND wt_date >= :begindate");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND wt_date <= :enddate");
		}
		sql.append(" AND wt_shop_code IN(:shopCodes)");
		sql.append(" AND wtl_pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY wtl_sub_code,wt_shop_code");
		sql.append(" )temp");
		sql.append(" GROUP BY wtl_sub_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("wtl_sub_code");
				String shop_amount = rs.getString("shop_amount");
				if(StringUtil.isEmpty(shop_amount)){
					return null;
				}
				String[] shopAmounts = shop_amount.split(",");
				for (SellStockByShopDepotDto dto : dtos) {
					if(!sub_code.equals(dto.getSub_code())){
						continue;
					}
					for (String shopAmount : shopAmounts) {
						String[] temps = shopAmount.split("#");
						dto.getShop_OnWayAmountMap().put(temps[0], Integer.parseInt(temps[1]));
					}
				}
				return null;
			};
		});
		
		//总部或分公司仓库库存
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.setLength(0);
			sql.append(" SELECT sd_code,GROUP_CONCAT(CONCAT(dp_code,'#',amount)) AS depot_amount");
			sql.append(" FROM(");
			sql.append(" SELECT sd_code,dp_code,SUM(sd_amount) AS amount");
			sql.append(" FROM t_stock_data t");
			sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND dp_shop_code = :shop_code");
			sql.append(" AND sd_pd_code = :pd_code");
			sql.append(" AND t.companyid = :companyid");
			sql.append(" GROUP BY sd_code,dp_code");
			sql.append(" )temp");
			sql.append(" GROUP BY sd_code");
			namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
					String sub_code = rs.getString("sd_code");
					String depot_amount = rs.getString("depot_amount");
					if(StringUtil.isEmpty(depot_amount)){
						return null;
					}
					String[] depotAmounts = depot_amount.split(",");
					for (SellStockByShopDepotDto dto : dtos) {
						if(!sub_code.equals(dto.getSub_code())){
							continue;
						}
						for (String depotAmount : depotAmounts) {
							String[] temps = depotAmount.split("#");
							dto.getDepot_StockAmountMap().put(temps[0], Integer.parseInt(temps[1]));
						}
					}
					return null;
				};
			});
		}
		return dtos;
	}
	
	@Override
	public List<SellStockBySizeDto> query_stock_detail_size(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cr_name,cr_code,IFNULL(br_code,'') AS br_code,IFNULL(br_name,'') AS br_name");
		sql.append(" FROM t_base_product t");
		sql.append(" JOIN t_base_product_color pdc ON pdc_pd_code = pd_code AND pdc.companyid = t.companyid");
		sql.append(" JOIN t_base_color cr ON cr_code = pdc_cr_code AND cr.companyid = pdc.companyid");
		sql.append(" LEFT JOIN t_base_product_br pdb ON pdb_pd_code = pd_code AND pdb.companyid = pdc.companyid");
		sql.append(" LEFT JOIN t_base_bra br ON br_code = pdb_br_code AND br.companyid = pdc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY cr_code,br_code");
		final List<SellStockBySizeDto> dtos = namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(SellStockBySizeDto.class));
		
		//销售数量
		sql.setLength(0);
		sql.append(" SELECT shl_cr_code,shl_br_code,GROUP_CONCAT(CONCAT(shl_sz_code,'#',amount)) AS size_amount");
		sql.append(" FROM(");
		sql.append(" SELECT shl_sub_code,shl_cr_code,shl_br_code,shl_sz_code,SUM(shl_amount) AS amount");
		sql.append(" FROM t_sell_shoplist");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND shl_date >= :begindate");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND shl_date <= :enddate");
		}
		if(StringUtil.isNotEmpty(params.get("em_code"))){
			sql.append(" AND shl_main = :em_code");
		}
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_pd_code = :pd_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY shl_sub_code");
		sql.append(" )temp");
		sql.append(" GROUP BY shl_cr_code,shl_br_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("shl_cr_code")+rs.getString("shl_br_code");
				String size_amount = rs.getString("size_amount");
				if(StringUtil.isEmpty(size_amount)){
					return null;
				}
				String[] sizeAmounts = size_amount.split(",");
				for (SellStockBySizeDto dto : dtos) {
					if(!sub_code.equals(dto.getCr_code()+dto.getBr_code())){
						continue;
					}
					for (String sizeAmount : sizeAmounts) {
						String[] temps = sizeAmount.split("#");
						dto.getSellAmountMap().put(temps[0], Integer.parseInt(temps[1]));
					}
				}
				return null;
			};
		});
		//库存数量
		sql.setLength(0);
		sql.append(" SELECT sd_cr_code,sd_br_code,GROUP_CONCAT(CONCAT(sd_sz_code,'#',amount)) AS size_amount");
		sql.append(" FROM(");
		sql.append(" SELECT sd_code,sd_cr_code,IFNULL(sd_br_code,'') AS sd_br_code,sd_sz_code,SUM(sd_amount) AS amount");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_depot dp ON dp_code = sd_dp_code AND dp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND dp_shop_code IN(:shopCodes)");
		sql.append(" AND sd_pd_code = :pd_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY sd_code");
		sql.append(" )temp");
		sql.append(" GROUP BY sd_cr_code,sd_br_code");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				String sub_code = rs.getString("sd_cr_code")+rs.getString("sd_br_code");
				String size_amount = rs.getString("size_amount");
				if(StringUtil.isEmpty(size_amount)){
					return null;
				}
				String[] sizeAmounts = size_amount.split(",");
				for (SellStockBySizeDto dto : dtos) {
					if(!sub_code.equals(dto.getCr_code()+dto.getBr_code())){
						continue;
					}
					for (String sizeAmount : sizeAmounts) {
						String[] temps = sizeAmount.split("#");
						dto.getStockAmountMap().put(temps[0], Integer.parseInt(temps[1]));
					}
				}
				return null;
			};
		});
		return dtos;
	}
}
		
